package com.bw.mapper;

import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Service;

import java.util.List;

@Mapper
public interface VoMapper {
    @Select("select bp.name,sum(oi.final_total_amount) 'value' from order_info oi\n" +
            "left join base_province bp on oi.province_id = bp.id\n" +
            "group by bp.name;")
    List<Vo> getList1();

    @Select("select b.name,sum(oi.final_total_amount) 'value' from order_info oi\n" +
            "left join order_detail o on oi.id = o.order_id\n" +
            "left join  sku_info sk on o.sku_id = sk.id\n" +
            "left join base_trademark b on sk.tm_id = b.id\n" +
            "group by b.name\n" +
            "order by value;")
    List<Vo> getList2();

    @Select("select b1.name,sum(oi.final_total_amount) 'value' from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join sku_info sk on od.sku_id = sk.id\n" +
            "left join  base_category3 b3 on sk.category3_id = b3.id\n" +
            "left join base_category2 b2 on b3.category2_id = b2.id\n" +
            "left join base_category1 b1 on b2.category1_id = b1.id\n" +
            "group by b1.name;")
    List<Vo> getList3();

    @Select("select '用户数(人)' name,a.num1,b.num2 from\n" +
            "(select count(*) num1 from user_info where timestampdiff(year,create_time,now())<1) a,\n" +
            "(select count(*) num2 from user_info where timestampdiff(year,create_time,now())>=1) b;")
    List<Vo1> getList4();
}
